package de.hs_zigr.inf.iib08gruppe5powerfish.vertrieb;
import java.sql.*; 
 
public class Database{
  String     driver     = "org.postgresql.Driver";
  String     host       = "";           
  String     port       = "";                
  String     database   = "";
  String     user       = "";                
  String     password   = "";    
  String	 schema 	= "";
 
  Connection connection = null;
Connection con;
ResultSet rs;
Statement stmt;
 // private static Object[] ds;
  public Database (String host, String port, String database, String user, String password, String schema) throws ClassNotFoundException, SQLException
  {
	this.host = host;
	this.port = port;
	this.database = database;
	this.user = user;
	this.password = password;
	this.schema = schema;
	Main.addMessage("Starting database...");
    loadJdbcDriver ();
    openConnection ();
    closeConnection ();
  }

  private void closeConnection ()
  {
    try
    {
      connection.close ();
    }
    catch (SQLException e)
    {
      e.printStackTrace ();
      System.exit (1);
    }
    Main.addMessage("database connection closed.");
  }
 

  private String getUrl ()
  {
 
    return ("jdbc:postgresql:" + (host != null ? ("//" + host) + (port != null ? ":" + port : "") + "/" : "") + database);
  }
 
 
 
  private void loadJdbcDriver () throws ClassNotFoundException
  {
    Class.forName (driver);
    Main.addMessage("driver " + this.driver + " loaded.");
  }
 
 
  private void openConnection () throws SQLException
  {
      connection = DriverManager.getConnection (getUrl (),
                                                user,
                                                password);
    Main.addMessage("connected to " + host + " database " + database + " (port " + port + ").");
  }
 
 /*
  public static void abfragen (){
    try {
  
     
      String url = "jdbc:postgresql://localhost:5432/Powerfish";
      Connection con = DriverManager.getConnection (url, user,password);
      Statement stmt =con.createStatement ( ResultSet.TYPE_SCROLL_INSENSITIVE, 
              ResultSet.CONCUR_UPDATABLE);
        String query ="SELECT * FROM produkte";
      
       Datensatz ds;
       ds = new Datensatz();
       ds.setProduktname(query);
    
      PreparedStatement getProduktname = con.prepareStatement(query);
      ResultSet rs = getProduktname.executeQuery();
 
      Main.addMessage("Abfrage: " + query);
      rs.close();
      stmt.close();
    }
   
      catch (SQLException exc){
        Main.addMessage ("SQLException: " + exc.getMessage());
      }
     
  }*/
  public void verbinde() throws SQLException {
    String url = "jdbc:postgresql://" + host + ":" + "5432/" + database;
     con = DriverManager.getConnection (url, user, password);
    stmt =con.createStatement ();
    String initQuery = "set search_path to " + schema;
    PreparedStatement init = con.prepareStatement(initQuery);
    init.execute();
    
    
    String query ="SELECT * FROM produkte";
    PreparedStatement getProduktname = con.prepareStatement(query);
    rs = getProduktname.executeQuery();
    rs.next();
    
    
  }
  
  Datensatz getNextDatensatz() throws SQLException{
	 Datensatz ds;
     ds = new Datensatz();

		ds.setProduktname(rs.getString("produktname"));

      ds.setHersteller(rs.getString("hersteller"));
      ds.setPreis(rs.getFloat("preis"));
      ds.setGewichtInKilo(rs.getFloat("gewichtInKilo"));
      ds.setVolumenInLiter(rs.getFloat("volumenInLiter"));
      ds.setStueckProPackung(rs.getInt("stueckProPackung"));
      ds.setProduktTyp(rs.getString("produktTyp"));
      ds.setProduktBild(rs.getString("produktBild"));
      ds.setBeschreibung(rs.getString("beschreibung"));
      
    
      String query1 ="SELECT fischname, wasserart FROM fisch f join fischfrisstfutter ff on f.fid = ff.fid WHERE ff.PID = "+ rs.getString("PID") + " order by wasserart desc;";
     
      PreparedStatement getFische = con.prepareStatement(query1,  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet rs1 = getFische.executeQuery();
      int i =0;
      
      while(!rs1.isLast()){rs1.next(); i++;}
      
      String[][] geeignet = new String[i][2];
    
      rs1.first();
      
      for(int j=0; j<i; j++){
    	  geeignet[j][0] = rs1.getString("fischname"); 
      geeignet[j][1] = rs1.getString("wasserart");
      rs1.next();
      }
      ds.setGeeignetFuer(geeignet);
      rs.next();
      return ds;
    
  }
  
  public boolean hasNext() throws SQLException{ 
	  return !rs.isAfterLast();
  }
  
  public void schliessen() throws SQLException{
    rs.close();
    stmt.close();
  
  }
}
